import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.read.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.magina.common.excel.model.EasyExcelModel;
import com.magina.common.excel.model.listen.ExcelListener;
import org.junit.Test;

import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelTestDemo {


    private static final String preFileName = "/root/magina/excel/";

    //每行数据是List<String>无表头
    @Test
    public void writeWithoutHead() throws IOException {
        try (OutputStream out = new FileOutputStream("/root/magina/excel/withoutHead.xlsx");) {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, false);
            Sheet sheet1 = new Sheet(1, 0);
            sheet1.setSheetName("sheet1");
            List<List<String>> data = new ArrayList<>();
            for (int i = 0; i < 100; i++) {
                List<String> item = new ArrayList<>();
                item.add("item0" + i);
                item.add("item1" + i);
                item.add("item2" + i);
                data.add(item);
            }
            writer.write0(data, sheet1);
            writer.finish();
        }
    }
    @Test
    public void writeWithHead() throws IOException {
        try (OutputStream out = new FileOutputStream(preFileName+"withHead.xlsx");) {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0);
            sheet1.setSheetName("sheet1");
            List<List<String>> data = new ArrayList<>();
            for (int i = 0; i < 100; i++) {
                List<String> item = new ArrayList<>();
                item.add("item0" + i);
                item.add("item1" + i);
                item.add("item2" + i);
                data.add(item);
            }
            List<List<String>> head = new ArrayList<List<String>>();
            List<String> headCoulumn1 = new ArrayList<String>();
            List<String> headCoulumn2 = new ArrayList<String>();
            List<String> headCoulumn3 = new ArrayList<String>();
            headCoulumn1.add("第一列");
            headCoulumn2.add("第二列");
            headCoulumn3.add("第三列");
            head.add(headCoulumn1);
            head.add(headCoulumn2);
            head.add(headCoulumn3);
            Table table = new Table(1);
            table.setHead(head);
            writer.write0(data, sheet1, table);
            writer.finish();
        }
    }

    @Test
    public void writeWithBean() throws IOException {
        try  {
            OutputStream out = new FileOutputStream(preFileName+"withBean_easyexcel_100.xlsx");
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, EasyExcelModel.class);
            sheet1.setSheetName("sheet1");
            List<EasyExcelModel> data = new ArrayList<>();
            for (int i = 0; i < 1000000; i++) {
                EasyExcelModel item = EasyExcelModel.builder()
                        .name("name" + i)
                        .age("age" + i)
                        .email("email" + i)
                        .address("address" + i)
                        .sax("sax" + i)
                        .loanDate(new Date())
                        .last("last" + i).build();
                data.add(item);
            }
            long start = System.currentTimeMillis();
            writer.write(data, sheet1);
            writer.finish();
            System.out.println(String.format("easyexcel end writer excel,time:%d",(System.currentTimeMillis()-start)));
            out.close();
        }catch (Exception e){
            System.out.println(e);
        }
    }

    @Test
    public void testExcel2003WithReflectModel() throws FileNotFoundException {
        InputStream inputStream = new FileInputStream(preFileName + "withBean.xlsx");
        try {
            // 解析每行结果在listener中处理
            AnalysisEventListener listener = new ExcelListener();
            ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, listener);
            excelReader.read(new Sheet(1, 0, EasyExcelModel.class));
            List<Sheet> sheetList = excelReader.getSheets();
            System.out.println("end");
        } catch (Exception e) {

        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }


    }
